This report aims to explore the impact of ‘Falling Rolls’ on academies, investigating lower pupil numbers resulting from declining populations in specific areas. The analysis is conducted for our customer, Alison Perry, to provide insights into challenges faced by academies in adapting to changing demographic trends.
1 BFR Forecasts vs AR Actuals for August 2023
Comparing the BFR forecast outturn for August 2023 with the AR actual outturn for the same period provides a crucial understanding of how well predictions align with the actual outcomes, enabling informed decisions for future planning.
This table provides overall summary of the AR actual and BFR forecasted Outturns for August 2023, grouped by Trust Type.
Note: “Difference” is calculated using formula: “AR(2023) - BFR(2023)”.
This Table provide trust level data for the AR actual and BFR forecasted Outturns for August 2023.
Note: “Difference” is calculated using formula: “AR(2023) - BFR(2023)”.
This Table Compared the Difference in AR actual and BFR forecast Outturns for August 2023, with the Percentage change in Pupil Numbers of the Trust in October 2023, as compared to previous year.
Note: “Pupil Numbers” corresponds to Number of Pupils in the Trust in October 2023 as per Autumn Census and “Pupil Change” signifies the percentage change in number of Pupils as compared to Previous Year (October 2022).
2 Pupil Numbers
Examining pupil numbers from the October 2022 and October 2023 school census, along with forecasted pupil numbers for Academic Years 2023/24, 2024/25 and 2025/26 from the BFR, offers valuable insights into enrollment trends. This analysis contributes to strategic planning by anticipating potential challenges and opportunities for academies in the coming years.
This table provides overall summary of the Pupil Numbers grouped by Trust Type.
Note: Median values for Year 2023/24, 2024/25 and 2025/26 is obtained using forecasted data in BFR.
This table provide trust level data for Number of Pupils for October 2022 and October 2023 using Autumn census data.
Note: “Pupil Change” signifies the percentage of change in pupil numbers in October 2023 when compared to previous year (October 2022).
This table provide trust level data for Number of Pupils for AY 2023/24, 2024/25 and 2025/26 using forecasted data from the BFR. Note: All columns are sortable.
This chart showcases the number of trusts with decline or increase in Pupil numbers from October 2022 to October 2023, categorised by Trust Type.
This chart showcases the distribution of trusts when compared based on the percentage of Pupil change from October 2022 to October 2023, categorised by Trust Type.
This chart showcases the distribution of trusts when compared based on the percentage of Pupil change from October 2022 to October 2023, categorised by Region.
3 Budgeted Staff Costs
The analysis for the percentage of income budgeted for staff costs in Academic Years 2023/24, 2024/25, and 2025/26 is currently a placeholder, pending the availability of relevant data. Once the necessary information is obtained, this section will be updated to provide insights into the allocation of income towards staff costs, aiding in a comprehensive understanding of financial planning for the specified academic years.
Source Code
---title: "Falling Rolls Analysis"author: "Shivansh Agnihotri"date: "03/05/2024"date-modified: last-modifiedformat: html: page-layout: full toc: true toc-location: left toc-expand: true code-tools: source: true toggle: false embed-resources: true number-sections: true smooth-scroll: true self-contained: true anchor-sections: trueexecute: echo: false---```{r}#| include: false#install.packages("tidyverse")#install.packages("odbc")#install.packages("dbplyr")#install.packages("readxl")#install.packages("reactable")#install.packages("plotly")library(reactable)library(tidyverse)library(odbc)library(dbplyr)library(readxl)library(plotly)library(htmltools)library(fontawesome)``````{r}# Connection to the databasecon <-dbConnect(odbc::odbc(), Driver ="SQL Server Native Client 11.0", Server ="T1PRANMSQL\\SQLPROD,60125", Database ="MA_AM_S_DATA", Trusted_Connection ="Yes",encoding ="LATIN1")# Adding Trust list data from excel to R tibbletrust_list <-read_excel("./Data/trust_list.xlsx") |>mutate(CompanyNumber =as.integer(CompanyNumber))# R function to calculate change ratiochange <-function(initial, final){return(((final-initial)/initial))}# Download as CSV Button Functioncsv_download <-function(Id, label ="Download as CSV", filename ="data.csv") { htmltools::tags$button(style="float: right;", label,onclick =sprintf("Reactable.downloadDataCSV('%s', '%s')", Id, filename) )}``````{r}#| cache: true#AR: Revenue Reserves#Total trust revenue reserves from Balance Sheet note in AR# 2022/23ar <-tbl( con, in_schema("FinancialReturns","vw_FR002_AccountReturn_Consolidated_03_Final_Aggregation")) |>filter(year ==2023& arunique =='BSM200 + BSM230') |>select(`AR 2023`= Amount, CompanyNumber = companynumber) |>collect()#BFR: Forecasted Balance#Cumulative net revenue income/expenditure for the # 2022/23bfr <-tbl( con, in_schema("FinancialReturns","vw_FR003_BFR_Consolidated_07_Combined")) |>filter(Year ==2023) |>select(CompanyNumber, `BFR 2023`="430") |>mutate(CompanyNumber =as.integer(CompanyNumber)) |>collect()``````{r}# Joined table of AR and BFRfinance <- ar |>inner_join(bfr, join_by(CompanyNumber))|>inner_join(trust_list, join_by(CompanyNumber)) |>mutate(Difference = (`AR 2023`-`BFR 2023`)) |>rename(Trust = trustname) |>filter(!is.na(Trust))``````{r}#| cache: true# Number of Pupils in the past 2 years data from the databasepupil <-dbGetQuery(con, "select * from MA_AM_S_DATA.[PupilNumbers].[vw_PN001_SchoolCensus_SchoolTotals] WHERE Census = 'Au' AND NationalCurriculumYear IN ('R','1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13')")``````{r}# Pupil Data for October 2022pupil_22 <- pupil |>filter(ACADYR =='2022/2023') |>group_by(CompaniesHouseNumber) |>summarise(`Pupil 2022`=sum(Pupils)) |>mutate(CompanyNumber =as.integer(CompaniesHouseNumber))# Pupil Data for October 2023pupil_23 <- pupil |>filter(ACADYR =='2023/2024'&!is.na(CompaniesHouseNumber)) |>group_by(CompaniesHouseNumber) |>summarise(`Pupil 2023`=sum(Pupils)) |>mutate(CompanyNumber =as.integer(CompaniesHouseNumber))# Joined table of pupils in 2022 and 2023pupil_actual <- pupil_22 |>inner_join(pupil_23, join_by(CompanyNumber)) |>inner_join(trust_list, join_by(CompanyNumber)) |>select(Trust = trustname,`2021/22`=`Pupil 2022`,`2022/23`=`Pupil 2023`,`Number of Academies`= Numberofacademies, LeadRSC) |>filter(!is.na(Trust), !is.na(`2022/23`)) |>mutate(`% Change`=change(`2021/22`, `2022/23`)) |>arrange(`% Change`)``````{r}#| cache: true# Pupil Numbers REFERENCES for the year 2023/24, 2024/25 and 2025/26 from databasepupil_ref <-dbGetQuery(con, "select * from MA_AM_S_DATA.[FinancialReturns].[vw_FR003_BFR3Y_SoFA_2023] WHERE EFALineNo = 900")# Pupil Numbers DATA for the year 2023/24, 2024/25 and 2025/26 from databasepupil_3y <-dbGetQuery(con, "select Reference, TrustUPIN, Category, Title, EFALineNo, Y2, Y3, Y4, comments, helptext from RA_Datasets.[BFR].[vw_BFR_Three_Year_Forecast_2023] where EFALineNo = 9000")``````{r}pupil_forecast <- pupil_ref |>select(CompaniesHouseNo, Reference) |>distinct() |>inner_join(pupil_3y, join_by(Reference)) |>inner_join(trust_list, join_by(CompaniesHouseNo == CompanyNumber)) |>select(Trust = trustname, `2023/24`= Y2,`2024/25`= Y3,`2025/26`= Y4)pupil_consolidated <- pupil_actual |>inner_join(pupil_forecast, join_by(Trust))``````{r}# Combined table of AR, BFR, Pupil Numbers and Trustscombined <- pupil_consolidated |>inner_join(finance, join_by(Trust))# Combined table with standard grouping of Trust Typescombined_final <- combined |>mutate(`Trust Type`=case_when( Numberofacademies ==1&`2022/23`<=500~"<=500 pupils (SAT)", Numberofacademies ==1&`2022/23`>500&`2022/23`<=1000~"501-1000 pupils (SAT)", Numberofacademies ==1&`2022/23`>1000~">1000 pupils (SAT)", Numberofacademies %in%2:3&`2022/23`<=1500~"<=1500 pupils (2 or 3 academies)", Numberofacademies %in%2:3&`2022/23`>1500~">1500 pupils (2 or 3 academies)", Numberofacademies %in%4:8&`2022/23`<=2000~"<=2000 pupils (4 to 8 academies)", Numberofacademies %in%4:8&`2022/23`>2000~">2000 pupils (4 to 8 academies)", Numberofacademies >8&`2022/23`<=4000~"<=4000 pupils (9+ academies)", Numberofacademies >8&`2022/23`>4000&`2022/23`<=7000~"4001-7000 pupils (9+ academies)", Numberofacademies >8&`2022/23`>7000~">7000 pupils (9+ academies)", ))# Summary Tablesummary <- combined_final |>group_by(`Trust Type`) |>summarise(`Median AR 2023`=median(`AR 2023`),`Median BFR 2023`=median(`BFR 2023`),`Median Difference`=`Median AR 2023`-`Median BFR 2023`,`Median Pupil 2021/22`=round(median(`2021/22`)),`Median Pupil 2022/23`=round(median(`2022/23`)),`Median Pupil 2023/24`=round(median(`2023/24`)),`Median Pupil 2024/25`=round(median(`2024/25`)),`Median Pupil 2025/26`=round(median(`2025/26`)))```This report aims to explore the impact of 'Falling Rolls' on academies, investigating lower pupil numbers resulting from declining populations in specific areas. The analysis is conducted for our customer, Alison Perry, to provide insights into challenges faced by academies in adapting to changing demographic trends.## BFR Forecasts vs AR Actuals for August 2023Comparing the BFR forecast outturn for August 2023 with the AR actual outturn for the same period provides a crucial understanding of how well predictions align with the actual outcomes, enabling informed decisions for future planning.::: nav-pills::: panel-tabset### SummaryThis table provides overall summary of the AR actual and BFR forecasted Outturns for August 2023, grouped by Trust Type.\Note: "Difference" is calculated using formula: "AR(2023) - BFR(2023)".```{r}csv_download("AR-BFR-Summary", fa("download"), filename ="AR-BFR-Summary.csv")summary |>select(`Trust Type`, `Median AR 2023`, `Median BFR 2023`,`Median Difference`) |>reactable(elementId ="AR-BFR-Summary", highlight =TRUE,striped =TRUE,bordered =TRUE,wrap =TRUE,columns =list('Trust Type'=colDef(minWidth =300),'Median Difference'=colDef(format =colFormat(currency ="GBP", separators =TRUE)),'Median AR 2023'=colDef(format =colFormat(currency ="GBP", separators =TRUE)),'Median BFR 2023'=colDef(format =colFormat(currency ="GBP", separators =TRUE)) ) )```### Table: Trust-Level AR/BFRThis Table provide trust level data for the AR actual and BFR forecasted Outturns for August 2023.\Note: "Difference" is calculated using formula: "AR(2023) - BFR(2023)".```{r}csv_download("AR-BFR-Trusts", fa("download"), filename ="AR-BFR-Trusts.csv")finance |>select(Trust, `AR 2023`, `BFR 2023`, Difference) |>arrange(Trust) |>reactable(elementId ="AR-BFR-Trusts",highlight =TRUE,striped =TRUE,bordered =TRUE,wrap =TRUE,columns =list('Trust'=colDef(minWidth =300, filterable =TRUE),'AR 2023'=colDef(format =colFormat(currency ="GBP", separators =TRUE)),'BFR 2023'=colDef(format =colFormat(currency ="GBP", separators =TRUE)),'Difference'=colDef(format =colFormat(currency ="GBP", separators =TRUE)) ) )```### Table: AR/BFR vs PupilThis Table Compared the Difference in AR actual and BFR forecast Outturns for August 2023, with the Percentage change in Pupil Numbers of the Trust in October 2023, as compared to previous year.\Note: "Pupil Numbers" corresponds to Number of Pupils in the Trust in October 2023 as per Autumn Census and "Pupil Change" signifies the percentage change in number of Pupils as compared to Previous Year (October 2022).```{r}csv_download("AR-BFR-Pupils", fa("download"), filename ="AR-BFR-Pupils.csv")combined |>select(Trust, Difference,`Pupil Change`=`% Change`,`Pupil Numbers`=`2022/23`) |>arrange(Trust) |>reactable(elementId ="AR-BFR-Pupils",highlight =TRUE,striped =TRUE,bordered =TRUE,wrap =TRUE,columns =list(Trust =colDef(minWidth =300, filterable =TRUE),'Pupil Numbers'=colDef(minWidth =80),'Pupil Change'=colDef(format =colFormat(digits =2, percent =TRUE)),'Difference'=colDef(minWidth =110,format =colFormat(currency ="GBP", separators =TRUE) ) ) )```### Chart: AR/BFR vs Pupil```{r}options(scipen =100)plot1 <- combined |>filter(`% Change`<1&`% Change`>-1) |>ggplot(aes(y=`% Change`, x=`Difference`, label = Trust,color =`2022/23`,alpha =0.7)) +geom_jitter() +scale_fill_continuous(type ="viridis", ) +theme_light() +labs(y ='% Change in Pupil Numbers',x ="Difference in AR and BFR (AR 2023 - BFR 2023)",color ='No. of Pupils\n in 2022/23 \n') +scale_y_continuous(labels = scales::percent)+scale_x_continuous(labels = scales::dollar_format(prefix ="£"))ggplotly(plot1)```::::::## Pupil NumbersExamining pupil numbers from the October 2022 and October 2023 school census, along with forecasted pupil numbers for Academic Years 2023/24, 2024/25 and 2025/26 from the BFR, offers valuable insights into enrollment trends. This analysis contributes to strategic planning by anticipating potential challenges and opportunities for academies in the coming years.::: nav-pills::: panel-tabset### SummaryThis table provides overall summary of the Pupil Numbers grouped by Trust Type.\Note: Median values for Year 2023/24, 2024/25 and 2025/26 is obtained using forecasted data in BFR.```{r}csv_download("Pupil-Summary", fa("download"), filename ="Pupil-Summary.csv")summary |>select(`Trust Type`, `Median Pupil 2021/22`,`Median Pupil 2022/23`,`Median Pupil 2023/24`, `Median Pupil 2024/25`, `Median Pupil 2025/26`) |>reactable(elementId ="Pupil-Summary",highlight =TRUE,striped =TRUE,bordered =TRUE,wrap =TRUE,columns =list('Trust Type'=colDef(minWidth =300) ) )```### Table: ActualsThis table provide trust level data for Number of Pupils for October 2022 and October 2023 using Autumn census data.\Note: "Pupil Change" signifies the percentage of change in pupil numbers in October 2023 when compared to previous year (October 2022).```{r}csv_download("Pupil-Actual", fa("download"), filename ="Pupil-Actual.csv")pupil_actual |>select(-LeadRSC, -`Number of Academies`, `Pupil Change`=`% Change`) |>reactable(elementId ="Pupil-Actual",highlight =TRUE,striped =TRUE,bordered =TRUE,wrap =TRUE,columns =list(Trust =colDef(minWidth =300, filterable =TRUE),`2021/22`=colDef(minWidth =80),`2022/23`=colDef(minWidth =80),`Pupil Change`=colDef(minWidth =100,format =colFormat(digits =2, percent =TRUE)) ) )```### Table: ForecastsThis table provide trust level data for Number of Pupils for AY 2023/24, 2024/25 and 2025/26 using forecasted data from the BFR. Note: All columns are sortable.```{r}csv_download("Pupil-Forecast", fa("download"), filename ="Pupil-Forecast.csv")pupil_consolidated |>select(Trust, `2021/22`, `2022/23`, `2023/24`, `2024/25`, `2025/26`) |>arrange(Trust) |>reactable(elementId ="Pupil-Forecast",highlight =TRUE,striped =TRUE,bordered =TRUE,wrap =TRUE,columns =list(Trust =colDef(minWidth =300, filterable =TRUE),`2021/22`=colDef(minWidth =80),`2022/23`=colDef(minWidth =80),`2023/24`=colDef(minWidth =80),`2024/25`=colDef(minWidth =80),`2025/26`=colDef(minWidth =80) ) )```### Chart: Pupil NumbersThis chart showcases the number of trusts with decline or increase in Pupil numbers from October 2022 to October 2023, categorised by Trust Type.```{r}plot2 <- combined_final |>mutate(people_change =case_when(`% Change`>=0~'Increment',`% Change`<0~'Decrement')) |>group_by(`Trust Type`, people_change) |>summarise(count =n(), .groups ="drop") |>ggplot(aes(x=`Trust Type`, y=count, fill=people_change)) +geom_bar(stat="identity", position="dodge") +theme_light() +labs(x ="Trust Type",y ='Number of Trusts',fill ='Pupil Numbers\n compared to\n Previous Year\n') +theme(axis.text.x =element_text(angle =45))ggplotly(plot2)```### Chart: DistributionThis chart showcases the distribution of trusts when compared based on the percentage of Pupil change from October 2022 to October 2023, categorised by Trust Type.```{r}plot3 <- combined_final |>mutate(`% Change`=100*`% Change`) |>filter(`% Change`<10&`% Change`>-10) |>ggplot(aes(x=`% Change`)) +geom_histogram(color="#BB2649", binwidth =1) +facet_wrap(~`Trust Type`, ncol =2) +theme_light() +labs(x ='% Change in Pupil Numbers',y ='Trusts')ggplotly(plot3, height =900)```### Chart: RegionThis chart showcases the distribution of trusts when compared based on the percentage of Pupil change from October 2022 to October 2023, categorised by Region.```{r}plot5 <- pupil_actual |>filter(`% Change`<0.2&`% Change`>-0.2) |>ggplot(aes(x =`% Change`)) +geom_histogram(color ="#FA7268", binwidth =0.01) +facet_wrap(~LeadRSC, ncol =2) +theme_light() +labs(x ='% Change in Pupil Numbers \n',y ='Trusts') +scale_x_continuous(labels = scales::percent)ggplotly(plot5, height =900)```::::::## Budgeted Staff CostsThe analysis for the percentage of income budgeted for staff costs in Academic Years 2023/24, 2024/25, and 2025/26 is currently a placeholder, pending the availability of relevant data. Once the necessary information is obtained, this section will be updated to provide insights into the allocation of income towards staff costs, aiding in a comprehensive understanding of financial planning for the specified academic years.